<?php

include_once "src/controller/dbmanagement/Connection.php";
include_once "src/model/Service.php";
include_once "src/model/ProvidedService.php";
include_once "src/model/State.php";
include_once "src/model/Consultant.php";
include_once "src/model/SearchResult.php";
include_once "src/model/RegistryCount.php";
include_once "src/regexception/DBConnectionException.php";
include_once "src/regexception/DBException.php";
include_once "src/regexception/GeneralRegistryException.php";

class AdminDBManager {

    public static function isAdminLogInValid($email, $password) {
        $isAdminLogInValid = false;

        try {
            
            $cnn = new Connection();
            
            $sql = "SELECT * FROM CONSULTANT WHERE Email = '" . $email . "' AND Account_Type_Id = 2";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) == 0) {
                throw new Exception("EmailDoesNotExistException");
            }

            $sql = "SELECT * FROM CONSULTANT WHERE Email = '" . $email . "' AND Account_Type_Id = 2 AND Password LIKE BINARY '" . $password ."'";
            $rs = $cnn->execute($sql);

            if (mysql_num_rows($rs) > 0) {
                $isAdminLogInValid = true;
            } else {
                throw new Exception("InvalidLoginException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $isAdminLogInValid;
    }

    public static function updateUserAccount($consultantId, $oldEmail, $newEmail,
        $newFirstName, $newLastName, $newPassword,
        $oldPassword, $changePasswordIndicator) {
        try {
            if ($oldEmail != $newEmail) {
                if (!DBManager::isEmailUnique($newEmail)) {
                    throw new GeneralRegistryException("DuplicateEmailException");
                }
            }

            $cnn = new Connection();

            $sqlUpdateConsultantAccount  = "UPDATE CONSULTANT SET ";
            $sqlUpdateConsultantAccount .= "First_Name = '" . str_replace("'", "\'", $newFirstName) . "', ";
            $sqlUpdateConsultantAccount .= "Last_Name = '" . str_replace("'", "\'", $newLastName) . "', ";
            $sqlUpdateConsultantAccount .= "Email = '" . $newEmail . "', ";

            if ($changePasswordIndicator == "1") {
                $sqlUpdateConsultantAccount .= "Password = '" . $newPassword . "', ";
            }

            $sqlUpdateConsultantAccount .= "Account_Last_Modification_Date = NOW() ";
            $sqlUpdateConsultantAccount .= "WHERE Consultant_Id = '" . $consultantId . "'";

            if (!$cnn->execute($sqlUpdateConsultantAccount)) {
                throw new GeneralRegistryException("UpdateAccountFailureException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function searchConsultants($searchGroupType, $consultantName,
        $email, $companyName, $servicesList, $pageNumber) {

        $searchResult = new SearchResult();
        try {

            $consultantNameParsed = explode(" ", str_replace("'", "\'", $consultantName));
            $companyNameParsed = explode(" ", str_replace("'", "\'", $companyName));

            $cnn = new Connection();

            $sql  = "SELECT CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email, CONSULTANT.Email ";
            $sql .= "FROM CONSULTANT LEFT JOIN CONSULTANT_OFFERED_SERVICES ON CONSULTANT.Consultant_Id = CONSULTANT_OFFERED_SERVICES.Consultant_Id ";
            $sql .= "WHERE CONSULTANT.Profile_Submitted_Indicator = 1 ";

            if ($searchGroupType == "0") {
                $sql .= "AND CONSULTANT.K12_Clientele_Indicator = 1 ";
            } else if ($searchGroupType == "1") {
                    $sql .= "AND CONSULTANT.Higher_Education_Clientele_Indicator = 1 ";
                }

            for ($i = 0; $i < count($consultantNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($consultantNameParsed)) {
                    $sql .= ") ";
                }
            }

            if ($email != "") {
                $sql .= "AND CONSULTANT.Profile_Email = '" . $email . "' ";
            }

            for ($i = 0; $i < count($companyNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($companyNameParsed)) {
                    $sql .= ") ";
                }
            }
            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            $sql .= ") ";
            */

            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            */
            $isFirstServiceCondition = true;
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    if ($isFirstServiceCondition) {
                        $sql .= "AND ( 1 <> 1 ";
                        $isFirstServiceCondition = false;
                    }
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            if (!$isFirstServiceCondition) {
                $sql .= ") ";
            }

            $sql .= "GROUP BY  CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";
            $sql .= "ORDER BY CONSULTANT.Profile_Last_Name ASC, CONSULTANT.Profile_First_Name ASC, CONSULTANT.Company_Name ASC ";
            $sql .= "LIMIT " . (($pageNumber) * 20) . ", 20";

            //echo $sql;

            $rs = $cnn->execute($sql);

            $consultants = array();
            $index = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $tempConsultant = new Consultant();
                $tempConsultant->setConsultantId($row["Consultant_Id"]);
                $tempConsultant->setCompanyName($row["Company_Name"]);
                $tempConsultant->setTelephone($row["Telephone"]);
                $tempConsultant->setWebsite($row["Website"]);
                $tempConsultant->setProfileFirstName($row["Profile_First_Name"]);
                $tempConsultant->setProfileLastName($row["Profile_Last_Name"]);
                $tempConsultant->setProfileEmail($row["Profile_Email"]);
                $tempConsultant->setEmail($row["Email"]);
                $consultants[$index] = $tempConsultant;
                $index++;
            }

            /* START QUERY TO COUNT TOTAL NUMBER OF RESULTS */

            $sql  = "SELECT CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";
            $sql .= "FROM CONSULTANT LEFT JOIN CONSULTANT_OFFERED_SERVICES ON CONSULTANT.Consultant_Id = CONSULTANT_OFFERED_SERVICES.Consultant_Id ";
            $sql .= "WHERE CONSULTANT.Profile_Submitted_Indicator = 1 ";

            if ($searchGroupType == "0") {
                $sql .= "AND CONSULTANT.K12_Clientele_Indicator = 1 ";
            } else if ($searchGroupType == "1") {
                    $sql .= "AND CONSULTANT.Higher_Education_Clientele_Indicator = 1 ";
                }

            for ($i = 0; $i < count($consultantNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Profile_First_Name LIKE '%" . $consultantNameParsed[$i] . "%' OR CONSULTANT.Profile_Last_Name LIKE '%" . $consultantNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($consultantNameParsed)) {
                    $sql .= ") ";
                }
            }

            if ($email != "") {
                $sql .= "AND CONSULTANT.Profile_Email = '" . $email . "' ";
            }

            for ($i = 0; $i < count($companyNameParsed); $i++) {
                if ($i == 0) {
                    $sql .= "AND ( CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                } else {
                    $sql .= "OR CONSULTANT.Company_Name LIKE '%" . $companyNameParsed[$i] . "%' ";
                }
                if (($i + 1) == count($companyNameParsed)) {
                    $sql .= ") ";
                }
            }

            /*
            if (count($servicesList) > 0) {
                $sql .= "AND ( 1 <> 1 ";
            } else {
                $sql .= "AND ( 1 = 1 ";
            }
            */
            $isFirstServiceCondition = true;
            for ($i = 0; $i < count($servicesList); $i++) {
                if ($servicesList[$i] != "false") {
                    if ($isFirstServiceCondition) {
                        $sql .= "AND ( 1 <> 1 ";
                        $isFirstServiceCondition = false;
                    }
                    $sql .= "OR CONSULTANT_OFFERED_SERVICES.Service_Id = '" . $servicesList[$i] . "' ";
                }
            }
            if (!$isFirstServiceCondition) {
                $sql .= ") ";
            }

            $sql .= "GROUP BY  CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Website, CONSULTANT.Profile_First_Name, ";
            $sql .= "CONSULTANT.Profile_Last_Name, CONSULTANT.Profile_Email ";

            //echo $sql;

            $rs = $cnn->execute($sql);

            $totalNumberResults = mysql_num_rows($rs);

            /* END QUERY TO COUNT TOTAL NUMBER OF RESULTS */

            $searchResult->setConsultants($consultants);
            $searchResult->setPageNumber($pageNumber);
            $searchResult->setTotalNumberResults($totalNumberResults);

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }

        return $searchResult;

    }

    public static function getRegistryCounts () {
        $registryCount = new RegistryCount();

        try {

            $cnn = new Connection();

            $sql = "SELECT COUNT(*) AS 'Total_Accounts_Created' FROM CONSULTANT WHERE CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalAccountsCreated($row["Total_Accounts_Created"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_Profiles_Published' FROM CONSULTANT WHERE Profile_Submitted_Indicator = 1 AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalProfilesPublished($row["Total_Profiles_Published"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_Profiles_Not_Published' FROM CONSULTANT WHERE (Profile_Submitted_Indicator = 0 OR Profile_Submitted_Indicator IS NULL) AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalProfilesNotPublished($row["Total_Profiles_Not_Published"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_K12_Profiles_Published' FROM CONSULTANT WHERE Profile_Submitted_Indicator = 1 OR K12_Clientele_Indicator = 1 AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalK12ProfilesPublished($row["Total_K12_Profiles_Published"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_Higher_Education_Profiles_Published' FROM CONSULTANT ";
            $sql .= "WHERE Profile_Submitted_Indicator = 1 OR Higher_Education_Clientele_Indicator = 1 AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalHigherEducationProfilesPublished($row["Total_Higher_Education_Profiles_Published"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_K12_Unique_Profiles_Published' FROM CONSULTANT ";
            $sql .= "WHERE  K12_Clientele_Indicator = 1 AND Profile_Submitted_Indicator = 1 ";
            $sql .= "AND (Higher_Education_Clientele_Indicator = 0 OR Higher_Education_Clientele_Indicator IS NULL) AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalK12UniqueProfilesPublished($row["Total_K12_Unique_Profiles_Published"]);
            }

            $sql = "SELECT COUNT(*) AS 'Total_Higher_Education_Unique_Profiles_Published' FROM CONSULTANT ";
            $sql .= "WHERE  Higher_Education_Clientele_Indicator = 1 AND Profile_Submitted_Indicator = 1 ";
            $sql .= "AND (K12_Clientele_Indicator = 0 OR K12_Clientele_Indicator IS NULL) AND CONSULTANT.Account_Type_Id = 1 ";
            $rs = $cnn->execute($sql);

            while ($row = mysql_fetch_assoc($rs)) {
                $registryCount->setTotalHigherEducationUniqueProfilesPublished($row["Total_Higher_Education_Unique_Profiles_Published"]);
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $registryCount;
    }

    public static function getAllUsersEmails () {
        $users = array();
        try {
            $cnn = new Connection();
            $sql = "SELECT Email FROM CONSULTANT";
            $rs = $cnn->execute($sql);
            $i = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $users[$i] = $row["Email"];
                $i++;
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $users;
    }

    public static function exportRegistryData () {
        $users = array();
        try {
            $cnn = new Connection();

            $sql  = "SELECT CONSULTANT.First_Name, CONSULTANT.Last_Name, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.Telephone, CONSULTANT.Email, CONSULTANT.Services_Offered_Description, ";
            $sql .= "CONSULTANT.K12_Clientele_Indicator, CONSULTANT.Higher_Education_Clientele_Indicator, CONSULTANT.Profile_Submitted_Indicator ";
            $sql .= "FROM CONSULTANT ";
            $sql .= "WHERE CONSULTANT.Account_Type_Id = 1 ";

            $rs = $cnn->execute($sql);
            
            $i = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $user = new Consultant();

                $user->setFirstName($row["First_Name"]);
                $user->setLastName($row["Last_Name"]);
                $user->setCompanyName($row["Company_Name"]);
                $user->setTelephone($row["Telephone"]);
                $user->setEmail($row["Email"]);
                $user->setServicesOfferedDescription($row["Services_Offered_Description"]);
                $user->setK12ClienteleIndicator($row["K12_Clientele_Indicator"]);
                $user->setHigherEducationClienteleIndicator($row["Higher_Education_Clientele_Indicator"]);
                $user->setProfileSubmittedIndicator($row["Profile_Submitted_Indicator"]);

                $users[$i] = $user;
                $i++;
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $users;
    }

    public static function deleteService ($serviceId) {
        try {
            $cnn = new Connection();

            $sql  = "DELETE FROM SERVICE ";
            $sql .= "WHERE Service_ID = '" . $serviceId . "'";

            if (!$cnn->execute($sql)) {
                throw new GeneralRegistryException("DeleteServiceFailureException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function addService($serviceName) {
        try {
            $cnn = new Connection();
            $sql = "INSERT INTO SERVICE SET ";
            $sql .="Service_Name = '" . $serviceName . "'";
            if (!$cnn->execute($sql)) {
                throw new GeneralRegistryException("Invalid Service Name");
            }
            $serviceAddedId = $cnn->getLastInsertId();
            $cnn->disconnect();
            return $serviceAddedId;
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function updateService($serviceId, $serviceName) {
        try {
            $cnn = new Connection();
            $sql = "UPDATE SERVICE SET ";
            $sql .="Service_Name = '" . $serviceName . "' ";
            $sql .="WHERE Service_Id = '" . $serviceId . "'";
            if (!$cnn->execute($sql)) {
                throw new GeneralRegistryException("Invalid Service Name");
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public static function exportConsultantServicesData () {
        $users = array();
        try {
            $cnn = new Connection();

            $sql  = "SELECT CONSULTANT.First_Name, CONSULTANT.Last_Name, SERVICE.Service_Name, CONSULTANT_OFFERED_SERVICES.Serving_Since ";
            $sql .= "FROM CONSULTANT LEFT OUTER JOIN CONSULTANT_OFFERED_SERVICES ON CONSULTANT.Consultant_Id = CONSULTANT_OFFERED_SERVICES.Consultant_Id ";
            $sql .= "LEFT OUTER JOIN SERVICE ON CONSULTANT_OFFERED_SERVICES.Service_ID = SERVICE.Service_Id ";
            $sql .= "WHERE CONSULTANT.Account_Type_Id = 1 ";
            $sql .= "ORDER BY First_Name, Last_Name";

            $rs = $cnn->execute($sql);

            $i = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $users[$i] = array(
                                "Consultant Name" => $row["First_Name"] . " " . $row["Last_Name"],
                                "Service" => $row["Service_Name"],
                                "Serving Since" => $row["Serving_Since"]);
                $i++;
            }
            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }
        return $users;
    }

    public static function getConsultantsAccountInformation($firstName, $lastName, $email) {
        $consultants = array();
        try {

            $cnn = new Connection();

            $sql  = "SELECT CONSULTANT.Consultant_Id, CONSULTANT.Company_Name, ";
            $sql .= "CONSULTANT.First_Name, CONSULTANT.Last_Name, ";
            $sql .= "CONSULTANT.Email ";
            $sql .= "FROM CONSULTANT ";
            $sql .= "WHERE CONSULTANT.Account_Type_Id = 1 ";
            $sql .= "AND CONSULTANT.First_Name LIKE '%" . $firstName . "%' AND CONSULTANT.Last_Name LIKE '%" . $lastName . "%' AND CONSULTANT.Email LIKE '%" . $email . "%' ";
            $sql .= "ORDER BY First_Name, Last_Name, Email ";

            //echo $sql;

            $rs = $cnn->execute($sql);

            $index = 0;
            while ($row = mysql_fetch_assoc($rs)) {
                $tempConsultant = new Consultant();
                $tempConsultant->setConsultantId($row["Consultant_Id"]);
                $tempConsultant->setCompanyName($row["Company_Name"]);
                $tempConsultant->setFirstName($row["First_Name"]);
                $tempConsultant->setLastName($row["Last_Name"]);
                $tempConsultant->setEmail($row["Email"]);
                $consultants[$index] = $tempConsultant;
                $index++;
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }

        return $consultants;

    }

    public static function updateUserEmailAccount($userId, $email) {
        try {
            /*
            if (!DBManager::isEmailUnique($email)) {
                throw new GeneralRegistryException("DuplicateEmailException");
            }
            */
            $cnn = new Connection();

            $sqlUpdateConsultantAccount  = "UPDATE CONSULTANT SET ";
            $sqlUpdateConsultantAccount .= "Email = '" . $email . "', ";

            $sqlUpdateConsultantAccount .= "Account_Last_Modification_Date = NOW() ";
            $sqlUpdateConsultantAccount .= "WHERE Consultant_Id = '" . $userId . "'";

            if (!$cnn->execute($sqlUpdateConsultantAccount)) {
                throw new GeneralRegistryException("UpdateAccountFailureException");
            }

            $cnn->disconnect();
        } catch (GeneralRegistryException $e) {
            throw $e;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        } catch (Exception $e) {
            throw $e;
        }
    }

}
?>
